﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using Dapper;
using Model;
using System.Data.SqlClient;
using System.Data;

namespace DapperDemo
{

    class Program
    {
        //public static string  x=>a?"b":"c";
        static void Main(string[] args)
        {
            #region  插入
            //    var user = new User {
            //        Name = "xiaogao",
            //        Phone = "15545485471"
            //    };

            //int insertInt= DbHelper.Excute("insert into [User] values(@Name,@Phone)",user);
            //int userid = DbHelper.GetModel<int>("select top 1 Id from [User]",null);
            //var list = new List<Book>();
            //for (int i = 0; i < 10; i++)
            //{
            //    Book book = new Book {
            //        BookName = "book" + i,
            //        Price=100,
            //        UserId=userid,
            //        CreateDate=DateTime.Now
            //    };
            //    list.Add(book);
            //}
            //int bookinsertInt = DbHelper.Excute("insert into Book values(@BookName,@Price,@UserId,@CreateDate)", list);
            //Console.WriteLine("insertInt:"+insertInt+"   bookinserint:"+bookinsertInt);
            #endregion
            #region 查询
            //1.0 两个表连表查询
            List<Book> books = DbHelper.MultiQuery<Book, User>("select * from Book as b inner join [User] as u on b.UserId=u.Id where u.Id=@Id",new { Id=3},(b,u)=> { b.user = u;return b; }).ToList();
            foreach (var item in books)
            {
                Console.WriteLine(item.BookName);
            }
            
            
            //2.0 返回动态数据
            List<dynamic> result = DbHelper.Query("select BookName,Price from Book as b inner join [User] as u on b.UserId=u.Id where b.Id>@Id",new { Id=3}).ToList();
            foreach (var item in result)
            {
                Console.WriteLine(string.Format("BookName:{0}--BookPrice:{1}",item.BookName,item.Price));
            }
            //3.0 返回多个数据集合
            using (IDbConnection conn = new SqlConnection(DbHelper.connstr))
            {
                conn.Open();
                //3.1
                using (var mulit = conn.QueryMultiple("select * from [User];select * from Book"))
                {
                    User user = mulit.Read<User>().First();
                    List<Book> mybooks = mulit.Read<Book>().ToList();
                    Console.WriteLine(string.Format("User>username:{0}",user.Name));
                    foreach (var item in mybooks)
                    {
                        Console.WriteLine(item.BookName);
                    }
                }
                //2.0 多表连接查询
                var sql = "select * from [User] as u inner join Book as b on u.Id=b.UserId inner join SendAddress as sa on sa.UserId=u.Id where u.Id=@Id";
                var mylist= conn.Query<User,Book,SendAddress,User>(sql,(u,b,sa)=> { u.Address.Add(sa); u.Books.Add(b);return u; },new {Id=3 }).ToList();
                foreach (var item in mylist)
                {
                    Console.WriteLine(string.Format("u.id:{0}--u.phone:{1}",item.Id,item.Phone));
                    foreach (var add in item.Address)
                    {
                        Console.WriteLine(string.Format("addressid:{0}--address:{1}",add.Id,add.Address));
                    }
                    foreach (var bk in item.Books)
                    {
                        Console.WriteLine(string.Format("bookid:{0}--book.name:{1}", bk.Id,bk.BookName));
                    }
                }

            }
            #endregion
            Console.Read();
        }
    }
}
